package com.telecom.cms.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.telecom.cms.util.Constant;
import com.telecom.cms.dto.*;
import com.telecom.cms.util.CurrentTime;

public class SEG_SUBJECT_TEMPLATE_DEFDAO {

	private static final String SQL_LIST = "select * from "
			+ Constant.SEG_SUBJECT_TEMPLATE_DEF
			+ " where Template_Type_Id= ? and User_Id = ? order by Template_Name";

	private static final String SQL_FINDBYNAME = "select * from "
			+ Constant.SEG_SUBJECT_TEMPLATE_DEF + " where Template_NAME = ?";

	private static final String SQL_FINDBYID = "select * from "
			+ Constant.SEG_SUBJECT_TEMPLATE_DEF + " where Template_ID = ?";

	private static final String SQL_INSERT = "insert into "
			+ Constant.SEG_SUBJECT_TEMPLATE_DEF
			+ " (Template_Name,Template_Desc,Template_Type_Id,User_Id,Create_Time,Latn_Id)Values(?,?,?,?,?,?)";

	private static final String SQL_DELETE = "delete from "
			+ Constant.SEG_SUBJECT_TEMPLATE_DEF + " where Template_Id = ?";

	private static final String SQL_MODIFY = "update "
			+ Constant.SEG_SUBJECT_TEMPLATE_DEF
			+ " set Template_Name= ? , Template_Desc= ? where Template_Id = ?";

	private Connection conn = null;

	public SEG_SUBJECT_TEMPLATE_DEFDAO(Connection con) {

		this.conn = con;

	}

	public List findAll(String type_id, String userId) {

		PreparedStatement psmt = null;
		ResultSet rs = null;
		List list = new ArrayList();
		try {
			psmt = conn.prepareStatement(SQL_LIST);
			psmt.setString(1, type_id);
			psmt.setString(2, userId);
			rs = psmt.executeQuery();
			while (rs.next()) {
				SEG_SUBJECT_TEMPLATE_DEFDTO SEG_SUBJECT_TEMPLATE_DEF = new SEG_SUBJECT_TEMPLATE_DEFDTO();
				SEG_SUBJECT_TEMPLATE_DEF.setTemplateName(rs
						.getString("Template_Name"));
				SEG_SUBJECT_TEMPLATE_DEF.setTemplateDesc("Template_Desc");
				SEG_SUBJECT_TEMPLATE_DEF
						.setTemplateId(rs.getInt("Template_Id"));
				SEG_SUBJECT_TEMPLATE_DEF.setTemplateTypeId(type_id);
				SEG_SUBJECT_TEMPLATE_DEF.setUserId(userId);
				list.add(SEG_SUBJECT_TEMPLATE_DEF);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (psmt != null) {
					psmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	public int findByName(String Template_Name) {

		PreparedStatement psmt = null;
		ResultSet rs = null;
		int id = 0;
		try {
			psmt = conn.prepareStatement(SQL_FINDBYNAME);
			psmt.setString(1, Template_Name);
			rs = psmt.executeQuery();
			if (rs.next())
				id = rs.getInt("Template_Id");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (psmt != null) {
					psmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return id;
	}

	public SEG_SUBJECT_TEMPLATE_DEFDTO findById(String Template_Id) {

		PreparedStatement psmt = null;
		ResultSet rs = null;
		SEG_SUBJECT_TEMPLATE_DEFDTO SEG_SUBJECT_TEMPLATE_DEF = new SEG_SUBJECT_TEMPLATE_DEFDTO();
		try {
			psmt = conn.prepareStatement(SQL_FINDBYID);
			psmt.setString(1, Template_Id);
			rs = psmt.executeQuery();
			if (rs.next()) {
				SEG_SUBJECT_TEMPLATE_DEF.setTemplateName(rs
						.getString("Template_Name"));
				SEG_SUBJECT_TEMPLATE_DEF.setTemplateDesc(rs
						.getString("Template_Desc"));
				SEG_SUBJECT_TEMPLATE_DEF
						.setTemplateId(rs.getInt("Template_Id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {

					rs.close();
				}
				if (psmt != null) {
					psmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return SEG_SUBJECT_TEMPLATE_DEF;
	}

	public void insert(SEG_SUBJECT_TEMPLATE_DEFDTO template) {

		PreparedStatement psmt = null;
		try {
			psmt = conn.prepareStatement(SQL_INSERT);
			psmt.setString(1, template.getTemplateName());
			psmt.setString(2, template.getTemplateDesc());
			psmt.setString(3, template.getTemplateTypeId());
			psmt.setString(4, template.getUserId());
			psmt.setString(5, CurrentTime.getCurrentTime());
			psmt.setString(6, template.getLatnId());

			psmt.execute();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (psmt != null) {
					psmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void modify(int templateId, SEG_SUBJECT_TEMPLATE_DEFDTO template) {

		PreparedStatement psmt = null;
		try {
			psmt = conn.prepareStatement(SQL_MODIFY);
			psmt.setString(1, template.getTemplateName());
			psmt.setString(2, template.getTemplateDesc());
			psmt.setInt(3, templateId);
			psmt.execute();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {

				if (psmt != null) {
					psmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void delete(int templateId) {

		PreparedStatement psmt = null;
		try {
			psmt = conn.prepareStatement(SQL_DELETE);
			psmt.setInt(1, templateId);
			psmt.execute();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {

				if (psmt != null) {
					psmt.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
}